package vn.edu.hut.soict.translate.database;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class TranslatorDatabase extends Database {
	public static final int NORMAL_MODE = 1;
	public static final int TEST_MODE = 2;

	private String bilingualTableName;

	public TranslatorDatabase() {
		super("localhost", 3306, "translator");
		bilingualTableName = "bilingual";
	}

	public TranslatorDatabase(int mode) {
		super("localhost", 3306, "translator");
		if (mode == NORMAL_MODE) {
			bilingualTableName = "bilingual";
		}
		else {
			bilingualTableName = "test";
		}
	}

	public String getBilingualTableName() {
		return bilingualTableName;
	}

	public List<String> getAllVietnameseSentences() {
		ResultSet results = null;
		List<String> sentences = new ArrayList<String>();
		try {
			Statement statement = connection.createStatement();
			results = statement.executeQuery("SELECT vietnamese FROM " + bilingualTableName);
			while (results.next()) {
				sentences.add(results.getString("vietnamese"));
			}
		}
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return sentences;
	}

	public String getEnglishSentence(String vietnamese) {
		try {
			Statement statement = connection.createStatement();
			ResultSet result = statement.executeQuery("SELECT english FROM " + bilingualTableName + " WHERE vietnamese = '" + vietnamese + "'");
			if (result.next()) {
				return result.getString(1);
			}
		}
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	public String getAlignment(String vietnamese) {
		try {
			Statement statement = connection.createStatement();
			ResultSet result = statement.executeQuery("SELECT align FROM " + bilingualTableName + " WHERE vietnamese = '" + vietnamese + "'");
			if (result.next()) {
				return result.getString(1);
			}
		}
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	public boolean insertRowToBilingual(String vietnamese, String english) {
		try {
			if (getEnglishSentence(vietnamese) == null) {
				PreparedStatement statement = connection.prepareStatement("INSERT INTO " + bilingualTableName + " (vietnamese, english) VALUES(?, ?)");
				statement.setString(1, vietnamese);
				statement.setString(2, english);
				return statement.executeUpdate() == 1;
			}
		}
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}

	public boolean insertRowToBilingual(String vietnamese, String english, String alignment) {
		try {
			if (getEnglishSentence(vietnamese) == null) {
				PreparedStatement statement = connection.prepareStatement("INSERT INTO " + bilingualTableName + " (vietnamese, english, align) VALUES(?, ?, ?)");
				statement.setString(1, vietnamese);
				statement.setString(2, english);
				statement.setString(3, alignment);
				return statement.executeUpdate() == 1;
			}
		}
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}

	public double getSimilarity(String word1, String word2) {
		try {
			PreparedStatement statement = connection.prepareStatement("SELECT similarity FROM similarity WHERE (word1 = ? AND word2 = ?) OR (word1 = ? AND word2 = ?)");
			statement.setString(1, word1);
			statement.setString(2, word2);
			statement.setString(3, word2);
			statement.setString(4, word1);
			ResultSet result = statement.executeQuery();
			if (result.next()) {
				return result.getDouble(1);
			}
		}
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return -1;
	}

	public boolean insertRowToSimilarity(String word1, String word2, double similarity) {
		try {
			PreparedStatement statement = connection.prepareStatement("INSERT INTO similarity (word1, word2, similarity) VALUES (?, ?, ?)");
			statement.setString(1, word1);
			statement.setString(2, word2);
			statement.setDouble(3, similarity);
			return statement.execute();
		}
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}

	public int getGoogleResultCount(String word) {
		try {
			Statement statement = connection.createStatement();
			ResultSet result = statement.executeQuery("SELECT count FROM googleresult WHERE word = '" + word + "'");
			if (result.next()) {
				return result.getInt(1);
			}
		}
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return -1;
	}

	public boolean insertRowToGoogleResultCount(String word, int count) {
		try {
			PreparedStatement statement = connection.prepareStatement("INSERT INTO googleresult (word, count) VALUES (?, ?)");
			statement.setString(1, word);
			statement.setInt(2, count);
			return statement.execute();
		}
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}
}
